package MySQLConnection;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import java.util.UUID;

import model.QualitativeValue;
import model.QuantitativeValue;
import model.ScoreValue;
import model.Value;

import common.Enums.MethodType;
import common.Enums.ReliabilityType;
import common.Enums.SourceType;
import common.Exceptions.DAOException;

import DAO.ValueDAO;

public class MySQLValueDAO implements ValueDAO 
{
	private final static String TABLE_NAME = MySqlDAOFactory.DBName + ".value"; 
	private static MySQLValueDAO instance;
	
	public static MySQLValueDAO getInstance()
	{
		if(instance == null)
			instance = new MySQLValueDAO();
		
		return instance;
	}
	
	@Override
	public void insert(Value item) throws DAOException 
	{
		String query = "INSERT INTO " + TABLE_NAME + "(val_id,val_unit, val_source, val_sourceType, val_reliability, val_type, val_createdDate, val_lastUpdated) " +
				"VALUES(?, ?, ?, ?, ?, ?, ?, ?);";

		try 
		{
			PreparedStatement preStatement = MySqlDAOFactory.getInstance().prepareStatement(query);
			preStatement.setString(1, item.getID().toString());
			preStatement.setString(2, item.getUnit());
			preStatement.setString(3, item.getSource());
			preStatement.setInt(4, MySqlDAOFactory.getEnumIdByName(MySqlDAOFactory.SOURCE_TYPE, item.getSourceType().toString()));
			preStatement.setInt(5, MySqlDAOFactory.getEnumIdByName(MySqlDAOFactory.RELIABILITY_TYPE, item.getReliability().toString()));
			preStatement.setInt(6, MySqlDAOFactory.getEnumIdByName(MySqlDAOFactory.METHOD_TYPE, item.getType().toString()));
			preStatement.setTimestamp(7, new java.sql.Timestamp(new java.util.Date().getTime()));
			preStatement.setTimestamp(8, new java.sql.Timestamp(new java.util.Date().getTime()));
			
			preStatement.executeUpdate();
			
			query = "INSERT INTO " + MySqlDAOFactory.DBName + "." + item.getClass().getSimpleName() + "(val_id, val_result)" + "VALUES('" + item.getID().toString() + "', '" + item.getStringResult() + "');";
			
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			statement.executeUpdate(query);
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The insert method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			MySqlDAOFactory.getInstance().closeConnection();
		}
	}

	@Override
	public void update(Value item) throws DAOException
	{
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE val_id = '" + item.getID().toString() + "';";

		try 
		{
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			if(dataSet.next())
			{
				String updateQuery = "UPDATE " + TABLE_NAME + " set val_Unit = ?, val_Source = ?, val_SourceType = ?, val_Reliability = ? , val_Type = ?, val_lastUpdated = ? WHERE val_id = ?";
				
				PreparedStatement preStatement = MySqlDAOFactory.getInstance().prepareStatement(updateQuery);
				preStatement.setString(1, item.getUnit());
				preStatement.setString(2, item.getSource());
				preStatement.setInt(3, MySqlDAOFactory.getEnumIdByName(MySqlDAOFactory.SOURCE_TYPE, item.getSourceType().toString()));
				preStatement.setInt(4, MySqlDAOFactory.getEnumIdByName(MySqlDAOFactory.RELIABILITY_TYPE, item.getReliability().toString()));
				preStatement.setInt(5, MySqlDAOFactory.getEnumIdByName(MySqlDAOFactory.METHOD_TYPE, item.getType().toString()));
				preStatement.setTimestamp(6, new java.sql.Timestamp(new java.util.Date().getTime()));
				preStatement.setString(7, item.getID().toString());
				preStatement.executeUpdate();
				
				String updateRelatedTable = "UPDATE " + item.getClass().getSimpleName() + " set val_result = valResult WHERE val_id = '" + item.getID() + "'";
				statement = MySqlDAOFactory.getInstance().createStatement();
				if(!statement.executeQuery("SELECT * FROM " + item.getClass().getSimpleName() + " WHERE val_id = '" + item.getID() + "'").next())
					updateRelatedTable = "INSERT INTO " + MySqlDAOFactory.DBName + "." + item.getClass().getSimpleName() + "(val_id, val_result)" + "VALUES('" + item.getID().toString() + "', valResult);";
					
				if(item.getType() == MethodType.QUALITATIVE)
					updateRelatedTable = updateRelatedTable.replace("valResult", "'" + item.getStringResult() + "'");
				else if(item.getType() == MethodType.QUANTITATIVE)
					updateRelatedTable = updateRelatedTable.replace("valResult", item.getStringResult());
				
				statement = MySqlDAOFactory.getInstance().createStatement();
				statement.executeUpdate(updateRelatedTable);
					
				if(dataSet.next())
					System.err.println("WARING: Two or more " + TABLE_NAME + " with the same ID were found in the database. Sol_id = " + item.getID());
			}
			else
				throw new DAOException("The " + TABLE_NAME + " identified by the id " + item.getID() + " hasn't been found in the database.");
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The update method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			MySqlDAOFactory.getInstance().closeConnection();
		}
	}

	@Override
	public void delete(UUID id) throws DAOException 
	{
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE val_id = '" + id + "';";

		try 
		{
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			if(dataSet.next())
			{
				String deleteQuery = "DELETE FROM " + TABLE_NAME + " WHERE val_id = '" + id + "'";
				
				statement = MySqlDAOFactory.getInstance().createStatement();
				if(statement.executeUpdate(deleteQuery) == 0)
					throw new DAOException("The " + TABLE_NAME + " identified by the id " + id + " couldn't be deleted due to an unknown error.");
					
				if(dataSet.next())
					System.err.println("WARING: Two or more " + TABLE_NAME + " with the same ID were found in the database. Val_id = " + id);
			}
			else
				throw new DAOException("The " + TABLE_NAME + " identified by the id " + id + " hasn't been found in the database.");
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The delete method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			MySqlDAOFactory.getInstance().closeConnection();
		}
		
	}

	@Override
	public List<Value> getAll() throws DAOException 
	{
		String query = "SELECT val_id FROM " + TABLE_NAME + ";";
		List<Value> values = new LinkedList<Value>();
		boolean lockOwner = false;

		try 
		{
			lockOwner = MySqlDAOFactory.lockConnection.tryLock();
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			while(dataSet.next())
			{
				UUID valID = UUID.fromString(dataSet.getString("val_id"));
				Value val = getByID(valID);
				values.add(val);
			}
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The getAll method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			if(lockOwner)
			{
				MySqlDAOFactory.lockConnection.unlock();
				MySqlDAOFactory.getInstance().closeConnection();
			}
		}
		
		return values;
	}

	@Override
	public Value getByID(UUID id) throws DAOException 
	{
		Value val = null;
		if(id != null)
		{
			String query = "SELECT * FROM " + TABLE_NAME + " WHERE val_id = '" + id.toString() + "';";

			try 
			{
				Statement statement = MySqlDAOFactory.getInstance().createStatement();
				ResultSet dataSet = statement.executeQuery(query);
				
				if(dataSet.next())
				{
					String unit = dataSet.getString("val_unit");
					String source = dataSet.getString("val_source");
					String reliability = MySqlDAOFactory.getEnumFromDatabase(MySqlDAOFactory.RELIABILITY_TYPE, dataSet.getString("val_reliability"));
					ReliabilityType reliabilityType = ReliabilityType.valueOf(reliability);
					String type = MySqlDAOFactory.getEnumFromDatabase(MySqlDAOFactory.METHOD_TYPE, dataSet.getString("val_type"));
					MethodType methodType = MethodType.valueOf(type);
					String srcType = MySqlDAOFactory.getEnumFromDatabase(MySqlDAOFactory.SOURCE_TYPE, dataSet.getString("val_sourceType"));
					SourceType sourceType = SourceType.valueOf(srcType);
					Date createdDate = new Date(dataSet.getTimestamp("val_createdDate").getTime());
					Date updatedDate = new Date(dataSet.getTimestamp("val_lastUpdated").getTime());
					
					String getRelatedDataQuery = "SELECT * FROM tableName WHERE val_id = '" + id + "';";
					
					if(methodType == MethodType.QUALITATIVE)
					{
						getRelatedDataQuery = getRelatedDataQuery.replace("tableName", QualitativeValue.class.getSimpleName());
						statement = MySqlDAOFactory.getInstance().createStatement();
						ResultSet relatedData = statement.executeQuery(getRelatedDataQuery);
						
						if(relatedData.next())
							val = new QualitativeValue(relatedData.getString("val_result"), id);
						else
							throw new DAOException("The QUALITATIVE value wasn't in the databse. Value result cannot be resolved.");
					}
					else if(methodType == MethodType.QUANTITATIVE)
					{
						getRelatedDataQuery = getRelatedDataQuery.replace("tableName", QuantitativeValue.class.getSimpleName());
						statement = MySqlDAOFactory.getInstance().createStatement();
						ResultSet relatedData = statement.executeQuery(getRelatedDataQuery);
						
						if(relatedData.next())
							val = new QuantitativeValue(Double.parseDouble(relatedData.getString("val_result")), id);
						else
						{
							System.err.println("WARING: The QUANTITATIVE value wasn't in the databse. Value result cannot be resolved.");
							return val;
						}
					}
					else
					{
						//TODO: Fetch the string result of the value in the database via query to the SCORE table...
						val = new ScoreValue(id);
					}
					
					val.setUnit(unit);
					val.setSource(source);
					val.setSourceType(sourceType);
					val.setReliability(reliabilityType);
					val.setCreatedDate(createdDate);
					val.setLastUpdated(updatedDate);
					
					if(dataSet.next())
						System.err.println("WARING: Two or more " + TABLE_NAME + " with the same ID were found in the database. Val_id = " + id);
				}
				else
					throw new DAOException("The " + TABLE_NAME + " identified by the id " + id + " hasn't been found in the database.");
			}
			catch (DAOException e) 
			{
				throw e;
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
				throw new DAOException("The getByID method failed due to some Query error: " + e.getMessage(), e);
			}
			finally
			{
				MySqlDAOFactory.getInstance().closeConnection();
			}
		}
		
		return val;
	}
}
